import XLSX from 'xlsx'
import FileSaver from 'file-saver'
import XLSXStyle from "xlsx-style";
const excel = {
    //将xlsx插件解析的数据转换为自定义数据结构,xlsx版本为0.17.X
    parseXlsxWorkbook(workbook) {
        if(!workbook) {
            return {};
        }
        let sheetList = [];
        let excelData = {sheetList:sheetList};
        for(let sheetName of workbook.SheetNames) {
            let sheet = workbook.Sheets[sheetName];
            // if(sheet.Hidden) {
            //   //隐藏标签页忽略
            //   continue;
            // }
            let sheetVO = this.parseXlsxSheet(sheet);
            if(!sheetVO) {
                continue;
            }
            sheetVO.name = sheetName;
            sheetList.push(sheetVO);
        }
        return excelData;
    },
    //将xlsx插件解析的数据转换为自定义数据结构,xlsx版本为0.17.X
    parseXlsxSheet(sheet) {
        let result = {};
        let dataRangeStr = sheet['!ref'];
        if(!dataRangeStr) {
            return null;
        }
        let dataRangeArr = dataRangeStr.split(':');
        if(!dataRangeArr||dataRangeArr.length!=2){
            return null;
        }
        let startPosition = this.parseExcelCellPosition(dataRangeArr[0]);
        let endPosition = this.parseExcelCellPosition(dataRangeArr[1]);
        let rowCount = endPosition.row - startPosition.row+1;
        let columnCount = endPosition.column - startPosition.column+1;

        let maxRow = 0;
        let maxCol = 0;
        let mergeList = [];
        //读取单元格合并信息
        let merges = sheet['!merges'];
        if(merges&&merges.length>0) {
            for(let item of merges) {
                let range = {};
                range.firstRow = item.s.r;
                range.firstColumn = item.s.c;
                range.lastRow = item.e.r;
                range.lastColumn = item.e.c;
                if(isNaN(range.firstRow)||isNaN(range.lastRow)||isNaN(range.firstColumn)||isNaN(range.lastColumn)){
                    continue;
                }
                mergeList.push(range);
            }
        }
        result.cellMergeList = mergeList;
        //读取数据行
        let rowList = [];
        for(let rowIndex=0;rowIndex<rowCount;rowIndex++) {
            let rowData = {};
            for(let colIndex =0;colIndex<columnCount;colIndex++) {
                let cellPosition = this.numToExcelColumnString(colIndex+1)+(rowIndex+1);
                let cell = sheet[cellPosition];
                if(!cell) {
                    continue;
                }
                let value = cell.v+'';
                if(value) {
                    rowData['col'+colIndex] = value;
                    if(maxCol<colIndex) {
                        maxCol = colIndex;
                    }
                    if(maxRow<rowIndex) {
                        maxRow = rowIndex;
                    }
                }
            }
            rowList.push(rowData);
        }
        let colIdList = [];
        for(let i=0;i<=maxCol;i++) {
            colIdList.push('col'+i);
        }
        result.colIdList = colIdList;
        result.dataList = rowList;
        return result;
    },
    //excel字符列名转数值
    excelStringColumnToNum(a) {
        let str = a.toLowerCase().split("");
        let num = 0;
        let al = str.length;
        let getCharNumber = function (charx) {
            return charx.charCodeAt() - 96;
        };
        let numout = 0;
        let charnum = 0;
        for (let i = 0; i < al; i++) {
            charnum = getCharNumber(str[i]);
            numout += charnum * Math.pow(26, al - i - 1);
        };
        return numout;
    },
    //数值转换为excel列字母
    numToExcelColumnString(numm) {
        let stringArray = [];
        let numToStringAction = function (nnum) {
            let num = nnum - 1;
            let a = parseInt(num / 26);
            let b = num % 26;
            stringArray.push(String.fromCharCode(64 + parseInt(b + 1)));
            if (a > 0) {
                numToStringAction(a);
            }
        }
        numToStringAction(numm);
        return stringArray.reverse().join("");
    },
    //根据excel单元格坐标转换为数值坐标
    parseExcelCellPosition(positionStr) {
        let result = {};
        function getNumIndex(str) {
            let pattern = new RegExp("[0-9]+");
            let num = str.match(pattern);
            return str.indexOf(num);
        }
        let numIndex = getNumIndex(positionStr);
        result.column=this.excelStringColumnToNum(positionStr.substring(0,numIndex))-1;
        result.row = parseInt(positionStr.substring(numIndex))-1;
        return result;
    },
    //导出excel文件
    exportExcel(config,name) {
        if(!config) {
            alert('未配置目标元素，无法导出')
            return ;
        }
        let sheetConfigList = [];
        if(config instanceof Array) {
            for(let index=1;index<=config.length;index++) {
                let item = config[index-1];
                if(!item){
                    continue;
                }
                let id = item.id;
                let name = item.name?item.name:'Sheet'+index;
                sheetConfigList.push({id:id,name:name});
            }
        } else if(config instanceof Object) {
            let id = config.id;
            let name = config.name?config.name:'Sheet1';
            sheetConfigList.push({id:id,name:name});
        } else {
            sheetConfigList.push({id:config+'',name:'Sheet1'});
        }
        /* generate workbook object from table */
        let xlsxParam = { raw: true } // 导出的内容只做解析，不进行格式转换
        let wb = XLSX.utils.book_new();
        for(let item of sheetConfigList) {
            let table = document.querySelector('#'+item.id);
            if(!table) {
                alert('无法找到dom:'+item.id+',无法导出');
            }
            table = table.cloneNode(true);
            let child = table.querySelector('.el-table__fixed');
            // if(child&&document.body.contains(child)) {
            if(child) {
                child.remove();
                // table.removeChild(child) //这里是双下划线
            }
            let wbSheet = XLSX.utils.table_to_sheet(table,xlsxParam);
            XLSX.utils.book_append_sheet(wb,wbSheet,item.name);
        }
        for(let sheetName in wb.Sheets) {
            let sheet = wb.Sheets[sheetName];
            this.setExcelStyle(sheet); // 设置列宽 字号等 如果无需多余的样式则省略
            if(sheet['!merges']) {
                this.addRangeBorder(sheet['!merges'], sheet) //设置合并行的border
            }
        }
        let wbout = XLSXStyle.write(wb, { type: 'buffer'})
        try {
            if(!name) {
                name = '导出文件';
            }
            FileSaver.saveAs(new Blob([wbout], { type: 'application/octet-stream' }), name+'.xlsx')
        } catch (e) {
            if (typeof console !== 'undefined') {
                console.error(e, wbout)
            }
        }
        return wbout
    },

    //设置样式
    setExcelStyle(data) {
        let borderAll = {  //单元格外侧框线
            top: {
                style: 'thin',
            },
            bottom: {
                style: 'thin'
            },
            left: {
                style: 'thin'
            },
            right: {
                style: 'thin'
            }
        };
        data['!cols'] = [];
        for (let key in data) {
            // console.log(key)
            if (data[key] instanceof Object) {
                data[key].s = {
                    border: borderAll,
                    alignment: {
                        horizontal: 'center',   //水平居中对齐
                        vertical:'center'
                    },
                    font:{
                        sz:11
                    },
                    bold:true,
                    numFmt: 0
                }
                data['!cols'].push({wpx: 115});
            }
        }
        return data;
    },
    //添加边框
    addRangeBorder (range, ws) {
        let cols = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
        range.forEach(item => {
            let style = {
                s: {
                    border: {
                        top: { style: 'thin' },
                        left: { style: 'thin' },
                        bottom: { style: 'thin' },
                        right: { style: 'thin' }
                    }
                }
            }
            // 处理合并行
            for (let i = item.s.c; i <= item.e.c; i++) {
                ws[`${cols[i]}${Number(item.e.r) + 1}`] = ws[`${cols[i]}${Number(item.e.r) + 1}`] || style
                // 处理合并列
                for (let k = item.s.r + 2; k <= item.e.r + 1; k++) {
                    ws[cols[i] + k] = ws[cols[k] + item.e.r] || style
                }
            }
        })
        return ws;
    },




};
export default excel;